import json
import random
import time
from urllib import request
from bs4 import BeautifulSoup

import mysql.connector
from fdfs_client.client import get_tracker_conf, Fdfs_client


def initMysql():
  global mydb
  global mycursor
  mydb = mysql.connector.connect(
    host='8.129.219.66',
    user='guifangtong',
    passwd='guifangtong1234!@#',
    database='guifangtong'
  )

  # mydb = mysql.connector.connect(
  #   host='localhost',
  #   user='root',
  #   passwd='root1234',
  #   database='guifangtong'
  # )
  mycursor = mydb.cursor()


def uploadPic(picurl):
  try:
    tracker_path = get_tracker_conf('./client.conf')
    client = Fdfs_client(tracker_path)
    # ret_upload
    # ret_upload = client.upload_by_filename('D://12.jpg') # 图片地址
    resp = request.urlopen(picurl)
    # image = np.asarray(bytearray(resp.read()), dtype="uint8")
    ret_upload = client.upload_by_buffer(resp.read())  # 图片地址
    # print(ret_upload['Remote file_id'])
    # print(ret_upload)
    return ret_upload['Remote file_id']
  except Exception as e:
    print('连接失败', e)


builds = ['南宁市龙光玖骏房地产开发有限公司', '南宁市耀辉房地产开发有限公司', '南宁市汉军房地产开发有限责任公司', '南宁市盈顺科技有限公司']
esteComs = ['龙光物业', '广东龙光集团物业管理有限公司南宁分公司', '深圳市深华物业集团有限公司南宁第一分公司', '世邦魏理仕']


def checkRMB(str):
  msg = str
  if str == '一':
    msg = 1
  elif str == '二' or str == '两':
    msg = 2
  elif str == '三':
    msg = 3
  elif str == '四':
    msg = 4
  elif str == '五':
    msg = 5
  elif str == '六':
    msg = 6
  elif str == '七':
    msg = 7
  elif str == '八':
    msg = 8
  elif str == '九':
    msg = 9
  elif str == '十':
    msg = 10
  return msg


'''
更新房源信息
'''


def updateFangXq(layout_obj):
  initMysql()
  table_name = 't_fang'
  # status -- status
  if '在售' in layout_obj['status']:
    status = 2
  else:
    status = 3

  details = layout_obj['details']
  detailss = ''
  for s in details:
    if s == '"':
      s = "'"
    detailss += s

  # print(detailss)
  # details.replace('"', '"',100)
  # print('details',)
  # UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

  # cmd_sql = 'UPDATE ' + table_name + ' set name = "' + layout_obj['name'] + '"  , status = ' + str(status) + ' and green_rat = "' + layout_obj['greeningrate'] \
  #           + '"  , floor_area_rat = "' + str( layout_obj['floorarearate'])  + '"  , estate_com = "' + layout_obj['propertycom'] + '"  , deliver_date = "' + \
  #           layout_obj['getdate_desc'] + '"  , property = "' + layout_obj['propertyright'] + '"  , area_pic = "' + layout_obj['planning_img'] + '"  , price = "' + layout_obj['average_price'] + '"  , price_detail = "' \
  #           +layout_obj['price_desc'] + '"  , location = "' + str(layout_obj['location']) + '"  , longitude = "' + str(layout_obj['longitude']) + '"  , latitude = "' + str(layout_obj['latitude']) \
  #           + '"  , build_address = "' + layout_obj['address'] + '"  , build_type_str = "' + str(layout_obj['build_type']) + '"  , hospital = "' + layout_obj['ambitus'] + '"  , special_str = "' + str(layout_obj['characteristic']) \
  #           + '"  , traffic = "' +  layout_obj['traffic'] + '"  , market = "' + layout_obj['service'] + '"  , school = "' + layout_obj['live_rule'] + '"  , content = "' + detailss \
  #           + '"  , extend = "' + layout_obj['extend'] + '"  , fix_type_str = "' + str(layout_obj['decorate']) + '"  , open_date = "' + layout_obj['opening_date'] + '"  , commission = "' + layout_obj['commission'] \
  #           + '"  , main_layout = "' + layout_obj['main_layout'] + '"  , block_count = "' + layout_obj['tung_count'] + '"  , for_sale_block = "' + layout_obj['saletung_count'] + '"  , site_area = "' + layout_obj['plan_area'] \
  #           + '"  , floor_area = "' + layout_obj['build_area'] + '"  , estate_fee = "' + layout_obj['property_charges'] + '"  , sale_address = "' + layout_obj['sale_address'] + '"  , area = "' + layout_obj['county'] \
  #           + '"  , planned_households = "' + layout_obj['plan_housecount'] + '"  , parking_lot_count = "' + layout_obj['plan_parkcount'] + '"  , hotline = "' + layout_obj['hotline'] + '"  , build_year = "' + layout_obj['build_years'] + '" where feature = ' + str(layout_obj['id'])

  traffic = layout_obj['traffic']
  traffics = ''
  for ss in traffic:
    if ss == '"':
      ss = "'"
    traffics += ss

  hospital = layout_obj['ambitus']
  hospitals = ''
  for sss in hospital:
    if sss == '"':
      sss = "'"
    hospitals += sss

  pic = layout_obj['planning_img']
  if pic != '':
    # print('上传前1', pic)
    pic = uploadPic(pic).decode('utf-8')
    # print('上传前2', pic)
    cmd_sql = 'UPDATE ' + table_name + ' set area_pic = "' + pic + '"  , traffic = "' + str(
      traffic) + '"  , hospital = "' + hospitals + '" where feature = ' + str(layout_obj['id'])
    print("更新sql", cmd_sql)
    try:
      mycursor.execute(cmd_sql)
      mydb.commit()
      print('更新成功了')
    except Exception as e:
      mydb.rollback()  # 发生错误时回滚
      print('更新出错了', e)
    mydb.close()
  else:
    print('暂无图片')
  # print('房源详情：', layout_obj)


def updateFangXqForLnglat(layout_obj):
  initMysql()
  table_name = 't_fang'
  longitude = layout_obj['longitude']
  latitude = layout_obj['latitude']
  cmd_sql = 'UPDATE ' + table_name + ' set longitude = "' + str(longitude) + '"  , latitude = "' + str(
    latitude) + '"  , lng_lat = "' + str(latitude) + ',' + str(longitude) + '" where feature = ' + str(
    layout_obj['id'])
  print(cmd_sql)
  try:
    mycursor.execute(cmd_sql)
    mydb.commit()
    print('更新成功了')
  except Exception as e:
    mydb.rollback()  # 发生错误时回滚
    print('更新出错了', e)
  mydb.close()


areaArr = [{'name': '兴宁', 'value': 1},{'name': '青秀', 'value': 2},{'name': '江南', 'value': 3},{'name': '良庆', 'value': 4},
           {'name': '西乡塘', 'value': 5},{'name': '邕宁', 'value': 6},{'name': '武鸣', 'value': 7}]


def updateFangXqForArea(layout_obj):
  initMysql()
  # private Integer area;//区域 1兴宁，2青秀，3江南，4良庆，5西乡塘，6邕宁，7武鸣
  # area = '良庆区'
  area = layout_obj['county']
  table_name = 't_fang'
  # areaIndex = 0
  # for areaItem in areaArr:
  #   if areaItem['name'] in area:
  #     print('if',area, areaItem['value'])
  #     areaIndex = areaItem['value']
  # cmd_sql = 'UPDATE ' + table_name + ' set area = "' + str(areaIndex) + '" where feature = ' + str(layout_obj['id'])
  # cmd_sql = 'UPDATE ' + table_name + ' set estate_type_str = "' + str(layout_obj['house_type']) + '" where feature = ' + str(layout_obj['id'])
  # cmd_sql = 'UPDATE ' + table_name + ' set estate_type_str = "' + str(layout_obj['house_type']) + '" where feature = ' + str(layout_obj['id'])
  print("更新sql", cmd_sql)
  try:
    mycursor.execute(cmd_sql)
    mydb.commit()
    print('更新成功了')
  except Exception as e:
    mydb.rollback()  # 发生错误时回滚
    print('更新出错了', e)
  mydb.close()


def sousuoLgbHx(fang_hengbao_id):
  # initMysql()
  # url = 'https://lgbao.logan.com.cn/olshow/v1/web/product/estate/query/recommend?city=450100&province=450000&recommendData=1'
  # url = 'https://hfb.hengfangbao.cn/api/public/houses?page='+str(page_index)+'&pageSize=100'
  url = 'https://hfb.hengfangbao.cn/api/public/biz_item?item_id=' + fang_hengbao_id
  print('请求地址', url)
  # 直接请求
  response = request.urlopen(url)
  # 获取状态码，如果是200表示成功
  # print(response.status)
  # 读取网页内容
  context = response.read().decode('utf-8')
  data = json.loads(context)
  if data['code'] == 1:
    layout_obj = data['result']
    # updateFangXq(layout_obj)
    # updateFangXqForLnglat(layout_obj)
    updateFangXqForArea(layout_obj)
  else:
    print(data['msg'])


def findFangs():
  initMysql()
  table_name = 't_fang'
  sql = "select * from " + table_name
  mycursor.execute(sql)
  myresult = mycursor.fetchall()  # fetchall() 获取所有记录
  return myresult


if __name__ == '__main__':
  # sousuoLgbHx()
  # strr = '3房5厅2卫'

  # strr = '四房两厅两卫'
  # shi = strr[0:1]
  # d = checkRMB(shi)
  # print(d)
  # ['6','63','232','330']
  # sousuoLgbHx('303')

  resFangs = findFangs()
  for item in resFangs:
    fang_id = item[0]
    fang_hengbao_id = item[12]
    sousuoLgbHx(fang_hengbao_id)
    time.sleep(2)

  # print(strr[0:1])
  # print(strr[2:3])
  # print(strr[4:5])
